﻿using Che.AppService.Dto;
using Che.Domain.Entity.EventsBut;
using Che.Domain.Entity.trophy;
using Che.Domain.repository.trophy;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace Che.EFCore.Repository.EFtrophy
{
    public class EFNOtrophy : Itrophy
    {
        private readonly MyDbContext myContext;

        public EFNOtrophy(MyDbContext myContext)
        {
            this.myContext = myContext;
        }

        public async Task<bool> addEvents(Event en)
        {
            try
            {
                myContext.Events.Add(en);
                await myContext.SaveChangesAsync();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("插入事件失败: " + ex.Message);
                return false;
            }
        }

        public async Task<List<EventDetail>> EventDetailque()
        {
            // 查询视图并返回结果
            var eventDetails = await myContext.vw_EventDetails
                                              .ToListAsync();

            return eventDetails;
        }

        public async Task<List<EventType>> eventTypes()
        {
            return await myContext.Eventstype.ToListAsync();
        }

        public async Task<List<FootballAdd>> footballAddsquest()
        {
            // 定义返回的结果列表
            var footballAdds = new List<FootballAdd>();

            // SQL 查询语句
            const string query = @"
            SELECT 
                et.EventsTypeId,
                et.Typeis,
                et.EventsTypeName,
                et.Region,
                et.EventsTypeTime,
                f.FootballId,
                f.FootballName,
                f.PlayingvenuesId,
                f.Founded,
                f.LeagueMatchesid,
                f.ContinentalId,
                f.Numberoftrophies,
                f.Src
            FROM Eventstype et
            LEFT JOIN Fottball f ON et.EventsTypeId = f.LeagueMatchesid
            ORDER BY et.EventsTypeId, f.FootballId;
        ";

            // 使用 Dapper 执行查询
            using (var connection = new SqlConnection("Data Source=CHELSEA;Initial Catalog=ChelseaFlootball2;Integrated Security=True;Trust Server Certificate=True;Integrated Security=True;Encrypt=False;"))
            {
                // 执行查询并映射结果
                var result = await connection.QueryAsync<EventType, Domain.Entity.EventsBut.Football, EventType>(
                    query,
                    (eventType, football) =>
                    {
                        // 查找或创建 FootballAdd 对象
                        var footballAdd = footballAdds.FirstOrDefault(fa => fa.EventsTypeId == eventType.EventsTypeId);
                        if (footballAdd == null)
                        {
                            footballAdd = new FootballAdd
                            {
                                EventsTypeId = eventType.EventsTypeId,
                                Typeis = eventType.Typeis,
                                EventsTypeName = eventType.EventsTypeName,
                                Region = eventType.Region,
                                EventsTypeTime = eventType.EventsTypeTime,
                                footballs = new List<Domain.Entity.EventsBut.Football>()
                            };
                            footballAdds.Add(footballAdd);
                        }

                        // 添加关联的 Football 数据
                        if (football != null)
                        {
                            footballAdd.footballs.Add(football);
                        }

                        return eventType;
                    },
                    splitOn: "FootballId" // 指定分割列
                );
            }

            // 返回结果
            return footballAdds;
        }

        public Task<List<Stadium>> footballs()
        {
            return myContext.Stadium.ToListAsync();
        }

        // 查询所有冠军详细信息
        public async Task<List<trophyq>> trophyqs()
        {
            // 直接查询所有奖杯数据
            return await myContext.trophy
                .AsNoTracking() // 只读操作，提升性能
                .OrderByDescending(t => t.year) // 按年份降序排列
                .ToListAsync(); // 异步返回结果
        }
    }
}